from dao.BaseDao import BaseDao
from Static import Static
import pandas as pd
from util import CommonHelper


class PatientDao(BaseDao):
    def initDB(self):
        self.__createTablePatient()

    def __createTablePatient(self):
        sql = """
            CREATE TABLE IF NOT EXISTS `{}` (
                `id` int NOT NULL AUTO_INCREMENT,
                `name` VARCHAR ( 20 ),
                `phone` VARCHAR ( 20 ) DEFAULT '-',
                `gender` VARCHAR ( 20 ) DEFAULT '1',
                `address` VARCHAR ( 255 ) DEFAULT '-',
                `birthday` date,
                `identity_code`  VARCHAR ( 20 ), 
                `outpatient_code` VARCHAR ( 20 ) DEFAULT '-',
                `inpatient_code` VARCHAR ( 20 ) DEFAULT '-',
            PRIMARY KEY ( `id` ) 
            ) ENGINE = INNODB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8;""".format(
            Static.DB_Patient
        )
        self.executeNoResult(sql)

    def importPatients(self, df):
        self.__createTablePatient()
        self.write2sql(
            df,
            Static.DB_Patient,
        )

    def getPatients(self, name, phone, identity_code, outpatient_code, inpatient_code):
        self.__createTablePatient()
        sql_query = """
            SELECT
                p.*,
                s.dictLabel genderStr 
            FROM
                `{}` p
                LEFT JOIN ( SELECT * FROM `{}` WHERE `key` = 'sys_user_sex' ) s ON s.dictValue = p.gender
        """.format(
            Static.DB_Patient, Static.DB_SystemDict
        )
        where_clause = []
        if name:
            where_clause.append(f"p.`name` LIKE '%%{name}%%'")
        if phone:
            where_clause.append(f"p.phone LIKE '%%{phone}%%'")
        if identity_code:
            where_clause.append(f"p.identity_code LIKE '%%{identity_code}%%'")
        if outpatient_code:
            where_clause.append(f"p.outpatient_code LIKE '%%{outpatient_code}%%'")
        if inpatient_code:
            where_clause.append(f"p.inpatient_code LIKE '%%{inpatient_code}%%'")
        if where_clause:
            sql_query += f" WHERE {' AND '.join(where_clause)}"
        df = self.readSQL(sql_query)
        return df

    def getPatientNames(self):
        self.__createTablePatient()
        sql = """
            SELECT 
                `id` `patient_id`,
                `name` `patientName`
            FROM `{}`
        """.format(
            Static.DB_Patient,
        )
        df = self.readSQL(sql)
        return df

    def checkPatientExist(self, name, identity_code):
        self.__createTablePatient()
        sql = """
            SELECT * FROM `{}` WHERE `name` = '{}' AND `identity_code` = '{}'
        """.format(
            Static.DB_Patient,
            name,
            identity_code,
        )
        df = self.readSQL(sql)
        return df

    def updatePatient(self, patient):
        self.__createTablePatient()
        sql = """
            UPDATE `{}` SET
                name = '{}',
                phone = '{}',
                gender = '{}',
                address = '{}',
                birthday = '{}',
                identity_code = '{}',
                outpatient_code = '{}',
                inpatient_code = '{}'
            WHERE id = {}
        """.format(
            Static.DB_Patient,
            patient["name"],
            patient["phone"],
            patient["gender"],
            patient["address"],
            patient["birthday"],
            patient["identity_code"],
            patient["outpatient_code"],
            patient["inpatient_code"],
            patient["id"],
        )
        self.executeNoResult(sql)

    def delPatient(self, patientId):
        self.__createTablePatient()
        sql = """
            DELETE FROM `{}` WHERE id = {}
        """.format(
            Static.DB_Patient,
            patientId,
        )
        self.executeNoResult(sql)

    def getPatientInfo(self, patientId):
        self.__createTablePatient()
        sql = """
            SELECT * FROM `{}` WHERE `id` = {}
        """.format(
            Static.DB_Patient,
            patientId,
        )
        df = self.readSQL(sql)
        return df
